package com.naiyumi.common;

import java.sql.*;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.logicalcobwebs.proxool.ProxoolException;
import org.logicalcobwebs.proxool.ProxoolFacade;
import org.logicalcobwebs.proxool.admin.SnapshotIF;
import org.springframework.stereotype.Repository;

@Repository
public class DB {
	private static Log log = LogFactory.getLog(DB.class);
	private Connection con = null;
	private ResultSet rs = null;

	public DB() {
//		try {
//			Class.forName("org.logicalcobwebs.proxool.ProxoolDriver");// proxool驱动类
//			// showSnapshotInfo();
//			// DriverManager.setLoginTimeout(2);
//			con = DriverManager.getConnection("proxool.DBPool");
//		} catch (Exception ex) {
//			log.error("创建DB类出错", ex);
//		}
	}

	public Connection getConnection() {
		return con;
	}

	/**
	 * 此方法可以得到连接池的信息
	 */
	@SuppressWarnings("unused")
	private void showSnapshotInfo() {
		try {
			int curActiveCount = 0;
			SnapshotIF snapshot = null;

			snapshot = ProxoolFacade.getSnapshot("DBPool", true);

			curActiveCount = snapshot.getActiveConnectionCount();// 获得活动连接数
			int availableCount = snapshot.getAvailableConnectionCount();// 获得可得到的连接数
			int maxCount = snapshot.getMaximumConnectionCount();// 获得最大连接数

			System.out.println(" 活动连接数:" + curActiveCount
					+ "(active)  可得到的连接数:" + availableCount
					+ "(available) 最大连接数:" + maxCount + "(max)");

		} catch (ProxoolException e) {
			log.error("显示状态快照出错", e);
		}
	}

	public int callProcedure(String name, String[] args) throws SQLException {
		int length = args.length;
		String params = "?";
		CallableStatement cast = null;
		for (int i = 0; i < length - 1; i++) {
			params += ",?";
		}
		if (length > 0 && length < 50) {
			try {
				cast = con.prepareCall("{call " + name + "(" + params + ")}");
				for (int i = 0; i < length; i++) {
					cast.setString(i + 1, args[i]);
				}
				cast.registerOutParameter(length, java.sql.Types.VARCHAR);
				cast.executeQuery();
				return cast.getInt(length);
			} finally {
				if (cast != null) {
					cast.close();
				}
				close();
			}
		} else {
			throw new RuntimeException("array.length is wrong");
		}
	}

	public int callFunction(String name, String[] args) throws SQLException {
		int length = args.length;
		String params = "?";
		CallableStatement cast = null;
		for (int i = 0; i < length - 1; i++) {
			params += ",?";
		}
		if (length > 0 && length < 50) {
			try {
				cast = con
						.prepareCall("{ ?=call " + name + "(" + params + ")}");
				for (int i = 1; i <= length; i++) {
					cast.setString(i + 1, args[i - 1]);
				}
				cast.registerOutParameter(1, java.sql.Types.INTEGER);
				cast.executeQuery();
				return cast.getInt(1);
			} finally {
				if (cast != null) {
					cast.close();
				}
				close();
			}
		} else {
			throw new RuntimeException("array.length is wrong");
		}
	}

	public String callFunStr(String name, String[] args) throws SQLException {
		int length = args.length;
		String params = "?";
		CallableStatement cast = null;
		for (int i = 0; i < length - 1; i++) {
			params += ",?";
		}
		if (length > 0 && length < 50) {
			try {
				cast = con
						.prepareCall("{ ?=call " + name + "(" + params + ")}");
				for (int i = 1; i <= length; i++) {
					cast.setString(i + 1, args[i - 1]);
				}
				cast.registerOutParameter(1, java.sql.Types.VARCHAR);
				cast.executeQuery();
				return cast.getString(1);
			} finally {
				if (cast != null) {
					cast.close();
				}
				close();
			}
		} else {
			throw new RuntimeException("array.length is wrong");
		}
	}

	/**
	 * 根据sql语句查询 return ResultSet
	 */
	public ResultSet querySql(String sql) {
		// System.out.println(sql);
		try {
			PreparedStatement pstmt = con.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = pstmt.executeQuery();
		} catch (SQLException e) {
			log.error("执行select 语句出错", e);
		}
		return rs;
	}

	/**
	 * 根据sql语句查询 return ResultSet
	 */
	public int execSql(String sql) {
		int ret = 0;
		try {
			PreparedStatement pstmt = con.prepareStatement(sql,
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			ret = pstmt.executeUpdate();
		} catch (SQLException e) {
			log.error("执行update 语句出错", e);
		}
		return ret;
	}

	/**
	 * 关闭连接
	 */
	public void close() {
		if (con != null) {
			try {
				con.close();
			} catch (SQLException e) {
				log.error("关闭链接出错", e);
			}

		}
	}

}
